<?php

class Search_model extends Model {

    function Search_model() {
        parent::Model();
    }

    function getContractorTypes() {
        $sql = 'SELECT * FROM contractor_type_lookup';
        $query = $this->db->query($sql);
        $result = $query->result_array();
        return $result;
    }

    function getContractorByContractorType($contractorTypeId) {
        $sql = "SELECT *
FROM contractor_type_lookup INNER JOIN contractor ON contractor_type_lookup.contractor_type_id = contractor.contractor_type_id
WHERE contractor_type_lookup.contractor_type_id = " . $contractorTypeId;
        $query = $this->db->query($sql);
        $result = $query->result_array();
        return $result;
    }

    function searchByState($stateId) {
//        $sql = "SELECT *
//        FROM (lcd_x_contractor INNER JOIN (contractor INNER JOIN state_lookup ON contractor.state_id = state_lookup.state_id) ON (lcd_x_contractor.contractor_version = contractor.contractor_version) AND (lcd_x_contractor.contractor_type_id = contractor.contractor_type_id) AND (lcd_x_contractor.contractor_id = contractor.contractor_id)) INNER JOIN lcd ON (lcd_x_contractor.lcd_id = lcd.lcd_id) AND (lcd_x_contractor.lcd_version = lcd.lcd_version)
//WHERE (((state_lookup.state_id)=" . $stateId . "))";
//
        // Select LCDs from Primary Jurisdiction
        $sql = "SELECT lcd_x_contractor.lcd_id, lcd_x_contractor.contractor_id, lcd_x_contractor.contractor_type_id, contractor_name, contractor_number
        FROM ((state_lookup INNER JOIN lcd_x_primary_jurisdiction ON state_lookup.state_id = lcd_x_primary_jurisdiction.state_id) INNER JOIN lcd_x_contractor ON lcd_x_primary_jurisdiction.lcd_id = lcd_x_contractor.lcd_id) INNER JOIN contractor ON lcd_x_contractor.contractor_id = contractor.contractor_id
WHERE (((state_lookup.state_id)=" . $stateId . "))";
        $query = $this->db->query($sql);
        $result = $query->result_array();

        // Select LCDs from Secondary Jurisdiction
        $sql = "SELECT lcd_x_contractor.lcd_id, lcd_x_contractor.contractor_id, lcd_x_contractor.contractor_type_id, contractor_name, contractor_number
        FROM (state_lookup INNER JOIN lcd_x_secondary_jurisdiction ON state_lookup.state_id = lcd_x_secondary_jurisdiction.state_id) INNER JOIN (lcd_x_contractor INNER JOIN contractor ON lcd_x_contractor.contractor_id = contractor.contractor_id) ON lcd_x_secondary_jurisdiction.lcd_id = lcd_x_contractor.lcd_id
WHERE (((state_lookup.state_id)=" . $stateId . "))";
        $query = $this->db->query($sql);
        $result = array_merge($result, $query->result_array());
        return $result;
    }

    function searchByTitle($title) {
        $sql = "SELECT lcd_x_contractor.lcd_id, lcd_x_contractor.contractor_id, lcd_x_contractor.contractor_type_id, contractor_name, contractor_number
FROM (lcd_x_contractor INNER JOIN contractor ON lcd_x_contractor.contractor_id = contractor.contractor_id) INNER JOIN lcd ON lcd_x_contractor.lcd_id = lcd.lcd_id
WHERE lcd.title like '%" . $title . "%'";
        $query = $this->db->query($sql);
        $result = $query->result_array();
        return $result;
    }

    function searchByContractorIds($contractorIds) {
        $sql = "SELECT lcd_x_contractor.lcd_id, lcd_x_contractor.contractor_id, lcd_x_contractor.contractor_type_id, contractor_name, contractor_number
FROM lcd INNER JOIN ((contractor_type_lookup INNER JOIN contractor ON contractor_type_lookup.contractor_type_id = contractor.contractor_type_id) INNER JOIN lcd_x_contractor ON (contractor.contractor_version = lcd_x_contractor.contractor_version) AND (contractor.contractor_type_id = lcd_x_contractor.contractor_type_id) AND (contractor.contractor_id = lcd_x_contractor.contractor_id)) ON (lcd.lcd_version = lcd_x_contractor.lcd_version) AND (lcd.lcd_id = lcd_x_contractor.lcd_id)
WHERE ";
        for ($i = 0; $i < count($contractorIds); $i++) {
            $sql.=" contractor.contractor_id=" . $contractorIds[$i];
            if ($i != (count($contractorIds) - 1))
                $sql.=" OR ";
        }
        $query = $this->db->query($sql);
        $result = $query->result_array();
        return $result;
    }

    function searchByStateAndTitle($stateId, $title) {
//        $sql = "SELECT *
//        FROM (lcd_x_contractor INNER JOIN (contractor INNER JOIN state_lookup ON contractor.state_id = state_lookup.state_id) ON (lcd_x_contractor.contractor_version = contractor.contractor_version) AND (lcd_x_contractor.contractor_type_id = contractor.contractor_type_id) AND (lcd_x_contractor.contractor_id = contractor.contractor_id)) INNER JOIN lcd ON (lcd_x_contractor.lcd_id = lcd.lcd_id) AND (lcd_x_contractor.lcd_version = lcd.lcd_version)
//WHERE (((state_lookup.state_id)=" . $stateId . ") AND title like '%" . $title . "%')";

        $result1 = $this->searchByState($stateId);
        $result2 = $this->searchByTitle($title);
        $result = array_merge($result1, $result2);
        $query = $this->db->query($sql);
//        $result = $query->result_array();
        //return $result;
    }

    function searchByTitleAndContractorIds($title, $contractorIds) {
        $sql = "SELECT *
        FROM (lcd_x_contractor INNER JOIN (contractor INNER JOIN state_lookup ON contractor.state_id = state_lookup.state_id) ON (lcd_x_contractor.contractor_version = contractor.contractor_version) AND (lcd_x_contractor.contractor_type_id = contractor.contractor_type_id) AND (lcd_x_contractor.contractor_id = contractor.contractor_id)) INNER JOIN lcd ON (lcd_x_contractor.lcd_id = lcd.lcd_id) AND (lcd_x_contractor.lcd_version = lcd.lcd_version)
WHERE title like '%" . $title . "%'";
        if (count($contractorIds) > 0) {
            $sql.=" AND (";
            for ($i = 0; $i < count($contractorIds); $i++) {
                $sql.=" contractor.contractor_id=" . $contractorIds[$i];
                if ($i != (count($contractorIds) - 1))
                    $sql.=" OR ";
            }
            $sql.=")";
        }
        $query = $this->db->query($sql);
        $result = $query->result_array();
        return $result;
    }

    function searchByStateAndContractorIds($stateId, $contractorIds) {
        $sql = "SELECT *
        FROM (lcd_x_contractor INNER JOIN (contractor INNER JOIN state_lookup ON contractor.state_id = state_lookup.state_id) ON (lcd_x_contractor.contractor_version = contractor.contractor_version) AND (lcd_x_contractor.contractor_type_id = contractor.contractor_type_id) AND (lcd_x_contractor.contractor_id = contractor.contractor_id)) INNER JOIN lcd ON (lcd_x_contractor.lcd_id = lcd.lcd_id) AND (lcd_x_contractor.lcd_version = lcd.lcd_version)
WHERE state_lookup.state_id=" . $stateId;
        if (count($contractorIds) > 0) {
            $sql.=" AND (";
            for ($i = 0; $i < count($contractorIds); $i++) {
                $sql.=" contractor.contractor_id=" . $contractorIds[$i];
                if ($i != (count($contractorIds) - 1))
                    $sql.=" OR ";
            }
            $sql.=")";
        }
        $query = $this->db->query($sql);
        $result = $query->result_array();
        return $result;
    }

    function searchByStateAndTitleAndContractorIds($stateId, $title, $contractorIds) {
        $sql = "SELECT *
        FROM (lcd_x_contractor INNER JOIN (contractor INNER JOIN state_lookup ON contractor.state_id = state_lookup.state_id) ON (lcd_x_contractor.contractor_version = contractor.contractor_version) AND (lcd_x_contractor.contractor_type_id = contractor.contractor_type_id) AND (lcd_x_contractor.contractor_id = contractor.contractor_id)) INNER JOIN lcd ON (lcd_x_contractor.lcd_id = lcd.lcd_id) AND (lcd_x_contractor.lcd_version = lcd.lcd_version)
WHERE (((state_lookup.state_id)=" . $stateId . ") AND title like '%" . $title . "%')";
        if (count($contractorIds) > 0) {
            $sql.=" AND (";
            for ($i = 0; $i < count($contractorIds); $i++) {
                $sql.=" contractor.contractor_id=" . $contractorIds[$i];
                if ($i != (count($contractorIds) - 1))
                    $sql.=" OR ";
            }
            $sql.=")";
        }
        $query = $this->db->query($sql);
        $result = $query->result_array();
        return $result;
    }

    function getAllStates() {
        $sql = "SELECT * FROM state_lookup";
        $query = $this->db->query($sql);
        $result = $query->result_array();
        return $result;
    }

    function getLCDById($lcdId) {
        $sql = "SELECT * from lcd WHERE lcd_id=" . $lcdId;
        $query = $this->db->query($sql);
        $result = $query->result_array();
        return $result;
    }

    function getHCPCDatabyLCDId($lcdId) {
        $sql = "SELECT hcpc_code_lookup.*
FROM hcpc_code_lookup INNER JOIN (lcd_x_hcpc_code INNER JOIN lcd ON (lcd_x_hcpc_code.lcd_id = lcd.lcd_id) AND (lcd_x_hcpc_code.lcd_version = lcd.lcd_version)) ON (hcpc_code_lookup.hcpc_code_id = lcd_x_hcpc_code.hcpc_code_id) AND (hcpc_code_lookup.hcpc_code_version = lcd_x_hcpc_code.hcpc_code_version)
WHERE (((lcd.lcd_id)=" . $lcdId . "))";
        $query = $this->db->query($sql);
        $result = $query->result_array();
        return $result;
    }

    function getIDC9DatabyLCDId($lcdId) {
        $sql = "SELECT icd9_code_lookup.*
FROM icd9_code_lookup INNER JOIN (lcd_x_icd9_support INNER JOIN lcd ON (lcd_x_icd9_support.lcd_version = lcd.lcd_version) AND (lcd_x_icd9_support.lcd_id = lcd.lcd_id)) ON (icd9_code_lookup.icd9_code_id = lcd_x_icd9_support.icd9_code_id) AND (icd9_code_lookup.icd9_code_version = lcd_x_icd9_support.icd9_code_version)
WHERE (((lcd.lcd_id)=" . $lcdId . "))";
        $query = $this->db->query($sql);
        $result = $query->result_array();
        return $result;
    }

    function getStateNameByStateId($stateId) {
        $sql = "SELECT * from state_lookup WHERE state_id=" . $stateId;
        $query = $this->db->query($sql);
        $result = $query->result_array();
        return $result;
    }

    function getLCDTitleById($lcdId)
    {
        $sql = "SELECT title from lcd WHERE lcd_id=" . $lcdId;
        $query = $this->db->query($sql);
        $result = $query->result_array();
        return $result[0]['title'];
    }

}

?>
